I recently had to build a trigger for the return management system for an eCommerce website. Essentially we had two tables: RETURN and RETURN_ITEM.

I had to make a trigger on insert on RETURN_ITEM, which raises an error and deletes a RETURN entity.

Let me preface this by saying that it is not a good practice to mix the RAISE clause with other statements. If you need to, then create multiple triggers. But at the same time, this solution might be useful in a variety of other cases.

The trigger looked something like this:

CREATE TRIGGER RETURN_VALIDATION
BEFORE INSERT ON RETURN_ITEM
FOR EACH ROW

BEGIN
  -- The trigger logic
END;

And the solution is as follows:

• Perform the CRUD operations with your trigger check in the WHERE clause
• If its a read operation, then you can just put that in your case with a SELECT 1 FROM … statement
• For everything else, use the changes() API in SQLite to check whether the CRUD operation occurred.
• Create a trigger case on the changes() API.

Like this:

DELETE FROM RETURN
 WHERE ticket_number = NEW.return_ticket_number AND
         NOT EXISTS (
          -- My Condition
            );
 SELECT CASE
        WHEN (SELECT changes() > 0) THEN RAISE(FAIL, 'Error')
    END;
END;